﻿using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;

namespace PSI.Services.TMS {
    public class InPlanReportService : PSIBaseService {
        /// <summary>
        /// 计划完成汇总表
        /// </summary>
        /// <returns></returns>
        public JArray GenReportCurrentMonth() {
            var maxday = DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
            var year = DateTime.Now.Year.ToString();
            var month = DateTime.Now.Month.ToString();
            var day = DateTime.Now.Day+1;
            var listDate = new List<string>();
            for (int i = day; i > 0; i--) {
                listDate.Add(year + "-" + month + "-" + i.ToString());
            }

            var sql = @"select distinct f_address from tms_in_consignation_detail
                        where f_plan_act_dt between @dt1 and @dt2 and f_status is not null
                        order by f_address";
            var p = CreateDbParameters();
            p.AddWithValue("dt1", listDate[listDate.Count - 1]);
            if (day > maxday)
            {
                var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                p.AddWithValue("dt2", dts);
            }
            else
            {
                p.AddWithValue("dt2", listDate[0]);
            }
            var listAddress = new List<string>();
            Query(sql, p, reader => {
                listAddress.Add(reader.GetString(0));
            });

            var result = new JArray();

            foreach (var address in listAddress) {
                var r = new JObject();
                r.Add("address", address);
                var index = 0;
                foreach (var dt in listDate) {
                    // 计划数
                    sql = @"select count(*) 
                            from tms_in_consignation_detail
                            where f_plan_act_dt = @dt and f_address = @address
                            ";
                    p = CreateDbParameters();
                    var dyd = dt.Split('-');
                    if (int.Parse(dyd[2]) > maxday)
                    {
                        var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                        p.AddWithValue("dt", dts);
                    }
                    else
                    {
                        p.AddWithValue("dt", dt);
                    }
                    p.AddWithValue("address", address);
                    var cnt1 = QueryCount(sql, p);

                    if (cnt1 == 0) {
                        r.Add("f_" + index.ToString() + "_1", null);

                    } else {
                        r.Add("f_" + index.ToString() + "_1", cnt1);
                    }
                    //完成数
                    sql = @"select count(*) 
                            from tms_in_consignation_detail
                            where f_plan_act_dt = @dt and f_address = @address and f_status = '运输完成'
                            ";
                    p = CreateDbParameters();
                    if (int.Parse(dyd[2]) > maxday)
                    {
                        var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                        p.AddWithValue("dt", dts);
                    }
                    else
                    {
                        p.AddWithValue("dt", dt);
                    }
                    p.AddWithValue("address", address);
                    var cnt2 = QueryCount(sql, p);
                    if (cnt2 == 0) {
                        r.Add("f_" + index.ToString() + "_2", null);
                    } else {
                        r.Add("f_" + index.ToString() + "_2", cnt2);
                    }
                    index++;
                }

                // 小计
                // 计划数
                sql = @"select count(*) 
                            from tms_in_consignation_detail
                            where (f_plan_act_dt between @dt1 and @dt2) 
                                and (f_address = @address)
                            ";
                p = CreateDbParameters();
                p.AddWithValue("address", address);
                p.AddWithValue("dt1", listDate[listDate.Count - 1]);
                if (day > maxday)
                {
                    var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                    p.AddWithValue("dt2", dts);
                }
                else
                {
                    p.AddWithValue("dt2", listDate[0]);
                }

                var sumCnt1 = QueryCount(sql, p);

                r.Add("sum1", sumCnt1);

                // 完成数
                sql = @"select count(*) 
                            from tms_in_consignation_detail
                            where (f_plan_act_dt between @dt1 and @dt2) 
                                and (f_address = @address) and (f_status = '运输完成')
                            ";
                p = CreateDbParameters();
                p.AddWithValue("address", address);
                p.AddWithValue("dt1", listDate[listDate.Count - 1]);
                if (day > maxday)
                {
                    var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                    p.AddWithValue("dt2", dts);
                }
                else
                {
                    p.AddWithValue("dt2", listDate[0]);
                }
                var sumCnt2 = QueryCount(sql, p);

                r.Add("sum2", sumCnt2);

                result.Add(r);
            }

            // 合计
            var item = new JObject();
            item.Add("address", "本月合计");
            // 计划数
            sql = @"select count(*)
                    from tms_in_consignation_detail
                    where (f_plan_act_dt between @dt1 and @dt2) 
                    ";
            p = CreateDbParameters();
            p.AddWithValue("dt1", listDate[listDate.Count - 1]);
            if (day > maxday)
            {
                var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                p.AddWithValue("dt2", dts);
            }
            else
            {
                p.AddWithValue("dt2", listDate[0]);
            }

            item.Add("sum1", QueryCount(sql, p));

            // 完成数
            sql = @"select count(*) 
                    from tms_in_consignation_detail
                    where (f_plan_act_dt between @dt1 and @dt2) 
                           and (f_status = '运输完成')
                    ";
            p = CreateDbParameters();
            p.AddWithValue("dt1", listDate[listDate.Count - 1]);
            if (day > maxday)
            {
                var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                p.AddWithValue("dt2", dts);
            }
            else
            {
                p.AddWithValue("dt2", listDate[0]);
            }

            item.Add("sum2", QueryCount(sql, p)); 
            
            {
                var index = 0;
                foreach (var dt in listDate) {
                    // 计划数
                    sql = @"select count(*) 
                            from tms_in_consignation_detail
                            where f_plan_act_dt = @dt
                            ";
                    p = CreateDbParameters();
                    var dyd = dt.Split('-');
                    if (int.Parse(dyd[2]) > maxday)
                    {
                        var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                        p.AddWithValue("dt", dts);
                    }
                    else
                    {
                        p.AddWithValue("dt", dt);
                    }
                    var cnt1 = QueryCount(sql, p);
                    if (cnt1 == 0) {
                        item.Add("f_" + index.ToString() + "_1", null);

                    } else {
                        item.Add("f_" + index.ToString() + "_1", cnt1);
                    }
                    //完成数
                    sql = @"select count(*) 
                            from tms_in_consignation_detail
                            where f_plan_act_dt = @dt and f_status = '运输完成'
                            ";
                    p = CreateDbParameters();
                    if (int.Parse(dyd[2]) > maxday)
                    {
                        var dts = (year + "-" + (DateTime.Now.AddMonths(1).Month) + "-1").ToString();
                        p.AddWithValue("dt", dts);
                    }
                    else
                    {
                        p.AddWithValue("dt", dt);
                    }
                    var cnt2 = QueryCount(sql, p);
                    if (cnt2 == 0) {
                        item.Add("f_" + index.ToString() + "_2", null);
                    } else {
                        item.Add("f_" + index.ToString() + "_2", cnt2);
                    }
                    index++;
                }
            }

            result.Insert(0, item);

            return result;
        }


        /// <summary>
        /// 进口计划汇总表
        /// </summary>
        /// <returns></returns>
        public JArray GetJKJHHZB(string dt)
        {
            var result = new JArray();
            try
            {
               
                var sql = new StringBuilder("select f_address,f_cnt_owner,");
                sql.Append("(select COUNT(*) from [tms_in_consignation_detail] where (f_cnt_type_2 like '%20%' or f_cnt_type_1 like '%20%') and f_address=a.f_address and f_cnt_owner=a.f_cnt_owner ) '20',");
                sql.Append("(select COUNT(*) from [tms_in_consignation_detail] where (f_cnt_type_2 like '%40%' or f_cnt_type_1 like '%40%') and f_address=a.f_address and f_cnt_owner=a.f_cnt_owner ) '40',");
                sql.Append("f_srv_memo");
                sql.Append(" from [tms_in_consignation_detail] a");
                sql.Append(" where f_plan_act_dt=@dt group by f_address,f_cnt_owner,f_srv_memo ");

                var p = CreateDbParameters();

                p.AddWithValue("dt", dt);

                var h20=0;
                var h40 = 0;


                Query(sql.ToString(), p, ready =>
                {
                    var item = new JObject();
                    item.Add("Address", ready.GetString(0));
                    item.Add("CntOwner", ready.GetString(1));
                    item.Add("CntType1", ready.GetInt32(2));
                    item.Add("CntType2", ready.GetInt32(3));
                    item.Add("Memo", ready.GetString(4));
                    h20 += ready.GetInt32(2);
                    h40 += ready.GetInt32(3);
                    result.Add(item);
                });

                var lastitem = new JObject();
                lastitem.Add("Address","合计");
                lastitem.Add("CntType1", h20);
                lastitem.Add("CntType2", h40);

                result.Add(lastitem);
                return result;
            }
            catch(Exception ex) {
                return result;
            }
        }


        public JArray ImANDEx(string dt)
        {
            var result = new JArray();
            var str = new StringBuilder("select f_plan_act_dt,f_address,f_cnt_owner,a20,a40,f_dt_3_plan,(case f_status when '运输完成' then (a20+a40) else 0 end),f_memo");
            str.Append(" from (select f_plan_act_dt,f_address,f_cnt_owner,");
            str.Append("(select COUNT(*) from [tms_in_consignation_detail] where (f_cnt_type_2 like '%20%' or f_cnt_type_1 like '%20%') and id=a.id) a20,");
            str.Append("(select COUNT(*) from [tms_in_consignation_detail] where (f_cnt_type_2 like '%40%' or f_cnt_type_1 like '%40%') and id=a.id) a40,");
            str.Append("f_dt_3_plan,f_memo,f_status from tms_in_consignation_detail a) as tab");
            str.Append(" where f_plan_act_dt=@dt");


            var strs = new StringBuilder("select f_plan_act_dt,f_address,f_cnt_owner,a20,a40,f_dt_4_plan,(case f_status when '运输完成' then (a20+a40) else 0 end),f_memo");
            strs.Append(" from (select f_plan_act_dt,f_address,f_cnt_owner,");
            strs.Append("(select COUNT(*) from [tms_out_consignation_detail] where (f_cnt_type_2 like '%20%' or f_cnt_type_1 like '%20%') and id=a.id) a20,");
            strs.Append("(select COUNT(*) from [tms_out_consignation_detail] where (f_cnt_type_2 like '%40%' or f_cnt_type_1 like '%40%') and id=a.id) a40,");
            strs.Append("f_dt_4_plan,f_memo,f_status from [tms_out_consignation_detail] a) as tab");
            strs.Append(" where f_plan_act_dt=@dt");


            var p = CreateDbParameters();

            p.AddWithValue("dt", dt);

            var h20 = 0;
            var h40 = 0;
            var num = 0;
            var num1 = 0;

            var hr= new JObject();
            hr.Add("Plandt", "进口");
            result.Add(hr);
            Query(str.ToString(), p, ready =>
            {
                var item = new JObject();
                item.Add("Plandt", ready.GetDateTime(0));
                item.Add("Address", ready.GetString(1));
                item.Add("CntOwner", ready.GetString(2));
                item.Add("CntType1", ready.GetInt32(3));
                item.Add("CntType2", ready.GetInt32(4));
                item.Add("PlanCompleteDt", ready.GetDateTime(5));
                item.Add("Competenum", ready.GetInt32(6));
                if (ready.GetInt32(6) == 0)
                {
                    item.Add("Nocompetenum", ready.GetInt32(3) + ready.GetInt32(4));
                    num1 += ready.GetInt32(3) + ready.GetInt32(4);
                }
                else
                {
                    item.Add("Nocompetenum", 0);
                }
                
                item.Add("Memo", ready.GetString(7));
                h20 += ready.GetInt32(3);
                h40 += ready.GetInt32(4);
                num += ready.GetInt32(6);
                result.Add(item);
            });

            var lastitem = new JObject();
            lastitem.Add("Plandt", "合计");
            lastitem.Add("CntType1", h20);
            lastitem.Add("CntType2", h40);
            lastitem.Add("Competenum", num);
            lastitem.Add("Nocompetenum", num1);
            result.Add(lastitem);

            var h20s = 0;
            var h40s = 0;
            var nums = 0;
            var num1s = 0;

            var hrs = new JObject();
            hrs.Add("Plandt", "出口");
            result.Add(hrs);
            Query(strs.ToString(), p, ready =>
            {
                var item = new JObject();
                item.Add("Plandt", ready.GetDateTime(0));
                item.Add("Address", ready.GetString(1));
                item.Add("CntOwner", ready.GetString(2));
                item.Add("CntType1", ready.GetInt32(3));
                item.Add("CntType2", ready.GetInt32(4));
                item.Add("PlanCompleteDt", ready.GetDateTime(5));
                item.Add("Competenum", ready.GetInt32(6));
                if (ready.GetInt32(6) == 0)
                {
                    item.Add("Nocompetenum", ready.GetInt32(3) + ready.GetInt32(4));
                    num1s += ready.GetInt32(3) + ready.GetInt32(4);
                }
                else
                {
                    item.Add("Nocompetenum", 0);
                }

                item.Add("Memo", ready.GetString(7));
                h20s += ready.GetInt32(3);
                h40s += ready.GetInt32(4);
                nums += ready.GetInt32(6);
                result.Add(item);
            });

            var lastitems = new JObject();
            lastitems.Add("Plandt", "合计");
            lastitems.Add("CntType1", h20s);
            lastitems.Add("CntType2", h40s);
            lastitems.Add("Competenum", nums);
            lastitems.Add("Nocompetenum", num1s);
            result.Add(lastitems);

            return result;
            
        }

    }
}